package com.iris.live.services.data.repositories;

import com.iris.live.services.data.models.顾客进店记录表Model;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

/**
 * Created by ThinkPad on 2016/8/30
 */
@Transactional
public interface 顾客进店记录表Repository extends JpaRepository<顾客进店记录表Model, Integer>, JpaSpecificationExecutor<顾客进店记录表Model> {
    //    @Query("select a from 顾客进店记录表Model a where a.报价编号=?1 and a.停留时间 is null")
//    @Query("select a from 顾客进店记录表Model a where a.报价编号=?1 and a.进店日期 = (select max(进店日期) from 顾客进店记录表Model where 报价编号 = ?1)")
// 20161130 由于注释的第二条语句无法取到顾客进店记录表Model，根据朱思捷的意思
// 先在PushApprovalWorkFlowRepositoryImpl中添加getValidQuotedPriceCode取出正确的报价编号，再传入这里
    @Query(value = "select * from 顾客进店记录表 a where a.报价编号=?1 limit 1",nativeQuery = true)
    顾客进店记录表Model findOneByQuotedPriceCode(int quotedPriceCode);

    @Modifying
    @Query("UPDATE 顾客进店记录表Model u SET u.离店时间=?1,u.停留时间='4',u.操作人='Admin@lsh.com',u.更新账号='Admin@lsh.com' WHERE u.进店编号=?2")
    void updLeavfTimeByEnterStoreNum(Date leavfDate,Integer enterStoreNum);

    @Query("select a from 顾客进店记录表Model a where a.顾客编号=?1 and a.许可=?2 and a.进店日期=?3 order by a.进店编号 desc")
    List<顾客进店记录表Model> findBy顾客编号And许可And进店日期(Integer 顾客编号, String 许可, java.sql.Date nowDate);

    @Modifying
    @Query("update 顾客进店记录表Model a set a.主动集客活动备注=?1 where a.进店编号=?2")
    void updateById(String remark,Integer id);

    @Query("select a from 顾客进店记录表Model a where a.顾客编号=?2 and a.许可=?1")
    List<顾客进店记录表Model> findByCusIdAndpermit(String permit, Integer cusId);

    @Query("select a from 顾客进店记录表Model a where a.许可 = ?1 and a.更新时间 < ?2 order by a.更新时间 desc")
    List<顾客进店记录表Model> findCusByPermintAndTime(String permit,Timestamp findTime);

//    @Query("select a from 顾客进店记录表Model a where a.报价编号=?1")
    @Query(value = "select * from 顾客进店记录表 a where a.报价编号=?1 ORDER BY a.进店编号 ASC  limit 1",nativeQuery = true)
    顾客进店记录表Model findByQuotedPriceCode(int quo);

    @Modifying
    @Query("update 顾客进店记录表Model a set a.是否零售=?2 where a.顾客编号=?1")
    int update是否零售ById(Integer id,String status);

    @Query("select a from 顾客进店记录表Model a where a.顾客编号=?1 and a.许可=?2 and a.进店日期>=?3 order by a.进店编号 desc")
    List<顾客进店记录表Model> findBy进店日期And许可(Integer 顾客编号, String 许可, java.sql.Date dt);


    @Query(value = "select g.报价编号 from 顾客进店记录表 g \n" +
            "INNER JOIN \n" +
            "\t(select 报价编号 from 报价单表 where 初始报价单号 = (select 初始报价单号 from \n" +
            "\t报价单表 where 报价编号 = ?1) ) s \n" +
            "ON g.报价编号 = s.报价编号  where g.进店日期 = CURDATE()  ORDER BY g.报价编号 desc limit 1",nativeQuery = true)
    String findBJBH(String BJBH);

    @Query("select a from 顾客进店记录表Model a where a.许可 = ?1")
    List<顾客进店记录表Model> getListByPermit(String permit);

    @Query(value = "select g.进店编号 from 顾客进店记录表 g \n" +
            "INNER JOIN \n" +
            "\t(select 报价编号 from 报价单表 where 初始报价单号 = (select 初始报价单号 from \n" +
            "\t报价单表 where 报价编号 = ?1) ) s \n" +
            "ON g.报价编号 = s.报价编号  where g.进店日期 = CURDATE()  ORDER BY g.报价编号 desc limit 1",nativeQuery = true)
    Integer findJDBH(String BJBH);

    @Query(value = "SELECT if(count(*)>0,'否','是') FROM\n" +
            "   顾客进店记录表  a , \n" +
            "   顾客记录汇总  b , \n" +
            "   报价单表 c \n" +
            "  WHERE a.顾客编号 = b.顾客编号 \n" +
            "  AND a.报价编号 = c.报价编号 \n" +
            "  AND b.电话号码 = ?1 \n" +
            "  AND b.许可 = ?2 \n" +
            "  AND NOT a.进店日期 = CURDATE() ",nativeQuery = true)
    String isFirstToStore (String Tel,String permit);

    @Query(value = "select a.* from 顾客进店记录表 a where 1=1 and  a.进店编号 = ?1 and a.进店日期=CURDATE()",nativeQuery = true)
    顾客进店记录表Model getOrderState(Integer inNo);


}
